package com.nf.db;

import com.nf.db.handler.ScalarHandler;
import com.nf.db.util.CleanerUtils;

import javax.sql.DataSource;
import java.sql.*;

/**
 * 此类是用来处理sql的增删查改(crud)
 */
public class SqlExecutor {

    private DataSource dataSource;

    /**
     * 这个无参的构造函数，表明此类的对象创建时是不需要DataSource对象的
     * 这样就可以让用户直接使用update方法（有Connection作为参数的）
     */
    public SqlExecutor() {
    }

    /**
     * 传递dataSource，通常意味用户会调用crud相关方法时，不再需要传递Connection了
     *
     * @param dataSource
     */
    public SqlExecutor(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    private static void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {
        // 处理参数值
        for (int i = 0; i < params.length; i++) {
            // 设置参数时，总是从1开始的，所以下面要加1
            stmt.setObject(i + 1, params[i]);
        }
    }

    public int update(String sql, Object... params) {
        Connection connection = getConnection();
        return update(connection, sql, params);
    }

    /**
     * 此方法用来实现增删改操作，完成之后会把连接主动关闭
     * 调用此方法之后，不能在对connection做出额外的操作（因为它已经关闭了）
     *
     * @param connection
     * @param sql
     * @param params
     * @return
     */
    public int update(Connection connection, String sql, Object... params) {

        int rows = 0;
        PreparedStatement stmt = null;
        try {
            stmt = connection.prepareStatement(sql);
            fillStatement(stmt, params);
            rows = stmt.executeUpdate();
        } catch (SQLException e) {
            throw new DaoException("数据库操作失败", e);
        } finally {
            CleanerUtils.closeQuietly(stmt, connection);
        }

        return rows;
    }

    public <T> T query(Connection connection, String sql, ResultSetHandler<T> handler, Object... params) {

        T result;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = connection.prepareStatement(sql);
            fillStatement(pstmt, params);
            rs = pstmt.executeQuery();
            result = handler.handle(rs);
        } catch (SQLException e) {
            throw new DaoException("查询数据库失败", e);
        } finally {
            CleanerUtils.closeQuietly(rs, pstmt, connection);
        }
        return result;

    }


    public <T> T insert(String sql, Object... params) {
        Connection connection = getConnection();
        return insert(connection, sql, params);
    }

    public <T> T insert(Connection connection, String sql, Object... params) {
        PreparedStatement stmt = null;
        T generatedKeys = null;
        try {
            //自增长列的关键处理代码1
            stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            fillStatement(stmt, params);
            stmt.executeUpdate();
            //自增长列的关键处理代码2
            try (ResultSet resultSet = stmt.getGeneratedKeys()) {
                // 自增长列结果集“通常”只有一列
                String columnName = resultSet.getMetaData().getColumnName(1);
                generatedKeys = (T) new ScalarHandler<>(columnName).handle(resultSet);
            }
        } catch (SQLException e) {
            throw new DaoException("数据库操作失败", e);
        } finally {
            CleanerUtils.closeQuietly(stmt, connection);
        }
        return generatedKeys;
    }

    /**
     * 此方法如果被子类重写，就可以对此方法的返回值进行额外的设定
     * Connection conn = super.getConnection();
     * conn.setXxx();
     * <p>
     * 如果真的子类要重写这个方法，子类是如果需要访问dataSource变量的话，
     * 那么就必须让子类可以访问datasource。两种方法实现
     * 改变dataSource字段的修饰符伟protected或者public
     * 另外一种方法就是提供一个getDataSource的方法，让子类访问
     *
     * @return
     */
    protected Connection getConnection() {
        Connection connection;
        try {
            connection = getDataSource().getConnection();

        } catch (SQLException e) {
            throw new DaoException("从datasource获取连接失败", e);
        }
        return connection;
    }

    protected DataSource getDataSource() {
        return dataSource;
    }

}
